package jsyf.oa.dao.capital;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.entity.capital.BankCardEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by houtailei on 2018/2/25.
 */
@Repository
public class BankCardDao extends BaseDao {

    public BankCardDao() {
        super.iBaseEntity = new BankCardEntity();
    }

    public List<BankCardEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<BankCardEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            BankCardEntity entity = (BankCardEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    public List<JSONObject> getBankCardList(String companyId,String accountApplication,String useStatus) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ID AS id,BANK_NAME AS bankName,BANK_CARD AS bankCard,INIT_MONEY AS initMoney, ");
        sb.append("(IFNULL(INIT_MONEY,0) + ");
        sb.append("(SELECT IFNULL(SUM(MONEY),0) FROM t_cpt_flow WHERE FLOW_DIRECTION = 1 AND t_cpt_flow.TK_BANK_CARD_ID = T_CPT_BANK_CARD.ID AND t_cpt_flow.DEL_FLAG=0) - ");
        sb.append("(SELECT IFNULL(SUM(MONEY),0) FROM t_cpt_flow WHERE FLOW_DIRECTION = 0 AND t_cpt_flow.TK_BANK_CARD_ID = T_CPT_BANK_CARD.ID AND t_cpt_flow.DEL_FLAG=0)) ");
        sb.append("as nowMoney, ");
        sb.append("ACCOUNT_APPLICATION AS account,USE_STATUS AS useStatus  ");
        sb.append("FROM T_CPT_BANK_CARD ");
        sb.append("WHERE 1=1 ");
        if(null!=companyId&&companyId.length()>0){
            sb.append(" AND FK_COMPANY_ID='" + companyId + "' ");
        }
        if(null!=accountApplication&&accountApplication.length()>0){
            sb.append(" AND ACCOUNT_APPLICATION='" + accountApplication + "' ");
        }
        if(null!=useStatus&&useStatus.length()>0){
            sb.append(" AND USE_STATUS='" + useStatus + "' ");
        }
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getCapital(String companyId, String date, String yestday) {
        StringBuffer sb = new StringBuffer();

        sb.append("(SELECT *,currentMoney+POSMoney CPlusP FROM ");
        sb.append("(SELECT currentMoney, dayIncome, dayExpenditure, yestdayMoney FROM ");
        sb.append("(SELECT ");
        sb.append("(SELECT SUM(INIT_MONEY) FROM t_cpt_bank_card WHERE FK_COMPANY_ID='" + companyId + "' AND ACCOUNT_APPLICATION!='保证金账户' AND ACCOUNT_APPLICATION!='外币户' AND ACCOUNT_APPLICATION!='POS机户' AND ACCOUNT_APPLICATION!='VCE专户' AND DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + date + "','%Y-%m-%d')) + ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END)- ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END) AS currentMoney, ");
        sb.append("(SELECT SUM(INIT_MONEY) FROM t_cpt_bank_card WHERE FK_COMPANY_ID='" + companyId + "' AND ACCOUNT_APPLICATION!='保证金账户' AND ACCOUNT_APPLICATION!='外币户' AND ACCOUNT_APPLICATION!='POS机户' AND ACCOUNT_APPLICATION!='VCE专户' AND DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + yestday + "','%Y-%m-%d')) + ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + yestday + "','%Y-%m-%d')) THEN MONEY else 0 END)- ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + yestday + "','%Y-%m-%d')) THEN MONEY else 0 END) AS yestdayMoney, ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END) AS dayIncome, ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END) AS dayExpenditure ");
        sb.append("FROM ");
        sb.append("(SELECT Y.*, FK_COMPANY_ID, ACCOUNT_APPLICATION FROM t_cpt_bank_card AS Z ");
        sb.append("LEFT JOIN t_cpt_flow AS Y ");
        sb.append("ON Y.TK_BANK_CARD_ID=Z.ID AND Y.DEL_FLAG=0 ");
        sb.append("WHERE Z.FK_COMPANY_ID='" + companyId + "' AND Z.ACCOUNT_APPLICATION!='保证金账户' AND Z.ACCOUNT_APPLICATION!='外币户' AND Z.ACCOUNT_APPLICATION!='POS机户' AND Z.ACCOUNT_APPLICATION!='VCE专户') AS X) AS A) AS C, ");
        sb.append("(SELECT debitsDayIncome, debitsDayExpenditure FROM ");
        sb.append("(SELECT ");
        sb.append("SUM(CASE WHEN DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')=DATE_FORMAT('" + date + "','%Y-%m-%d') THEN MONEY ELSE 0 END) ");
        sb.append("AS debitsDayIncome, ");
        sb.append("SUM(CASE WHEN ENDORSE_FLAG=1 AND DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')=DATE_FORMAT('" + date + "','%Y-%m-%d') THEN MONEY ELSE 0 END) ");
        sb.append("AS debitsDayExpenditure ");
        sb.append("FROM t_cpt_bank_debits WHERE FK_COMPANY_ID='" + companyId + "' AND DEL_FLAG=0) AS B) AS D, ");
        sb.append("(SELECT ");
        sb.append("(SELECT SUM(INIT_MONEY) FROM t_cpt_bank_card WHERE FK_COMPANY_ID='" + companyId + "' AND ACCOUNT_APPLICATION='VCE专户' AND DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + date + "','%Y-%m-%d')) + ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END)- ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END) AS VCEMoney ");
        sb.append("FROM ");
        sb.append("(SELECT Y.*, FK_COMPANY_ID, ACCOUNT_APPLICATION FROM t_cpt_bank_card AS Z ");
        sb.append("LEFT JOIN t_cpt_flow AS Y ");
        sb.append("ON Y.TK_BANK_CARD_ID=Z.ID ");
        sb.append("WHERE Z.FK_COMPANY_ID='" + companyId + "' AND Z.ACCOUNT_APPLICATION='VCE专户') AS X) AS E, ");
        sb.append("(SELECT ");
        sb.append("IFNULL(SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END),0)- ");
        sb.append("IFNULL(SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + date + "','%Y-%m-%d')) THEN MONEY else 0 END),0) AS POSMoney ");
        sb.append("FROM ");
        sb.append("(SELECT Y.*, FK_COMPANY_ID, ACCOUNT_APPLICATION FROM t_cpt_bank_card AS Z ");
        sb.append("LEFT JOIN t_cpt_flow AS Y ");
        sb.append("ON Y.TK_BANK_CARD_ID=Z.ID ");
        sb.append("WHERE Z.FK_COMPANY_ID='" + companyId + "' AND Z.ACCOUNT_APPLICATION='POS机户') AS X) AS F) ");

        JSONObject jsonObject = super.queryForJsonList(sb.toString()).get(0);
        return jsonObject;
    }


    public JSONObject getFundsTotal(String yestday) {
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT currentMoney, yestdayMoney FROM ");
        sb.append("(SELECT ");
        sb.append("(SELECT SUM(INIT_MONEY) FROM t_cpt_bank_card WHERE ACCOUNT_APPLICATION!='保证金账户' AND ACCOUNT_APPLICATION!='外币户' AND ACCOUNT_APPLICATION!='POS机户' AND ACCOUNT_APPLICATION!='VCE专户' AND DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d')) + ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN MONEY else 0 END)- ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN MONEY else 0 END) AS currentMoney, ");
        sb.append("(SELECT SUM(INIT_MONEY) FROM t_cpt_bank_card WHERE ACCOUNT_APPLICATION!='保证金账户' AND ACCOUNT_APPLICATION!='外币户' AND ACCOUNT_APPLICATION!='POS机户' AND ACCOUNT_APPLICATION!='VCE专户' AND DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + yestday + "','%Y-%m-%d')) + ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + yestday + "','%Y-%m-%d')) THEN MONEY else 0 END)-  ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(LEFT(CREATE_TIME,10),'%Y-%m-%d')<=DATE_FORMAT('" + yestday + "','%Y-%m-%d')) THEN MONEY else 0 END) AS yestdayMoney, ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=1 AND (DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN MONEY else 0 END) AS dayIncome, ");
        sb.append("SUM(CASE WHEN FLOW_DIRECTION=0 AND (DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')) THEN MONEY else 0 END) AS dayExpenditure ");
        sb.append("FROM ");
        sb.append("(SELECT Y.*, FK_COMPANY_ID, ACCOUNT_APPLICATION FROM t_cpt_bank_card AS Z ");
        sb.append("LEFT JOIN t_cpt_flow AS Y ");
        sb.append("ON Y.TK_BANK_CARD_ID=Z.ID AND Y.DEL_FLAG=0 ");
        sb.append("WHERE Z.ACCOUNT_APPLICATION!='保证金账户' AND Z.ACCOUNT_APPLICATION!='外币户' AND Z.ACCOUNT_APPLICATION!='POS机户' AND Z.ACCOUNT_APPLICATION!='VCE专户') AS X) AS A ");
        JSONObject jsonObject = super.queryForJsonList(sb.toString()).get(0);
        return jsonObject;
    }

    public JSONObject getDebits(String companyId, String date) {
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT A.debitsIncome-A.debitsExpenditure AS currentDebits FROM \n");
        sb.append("(SELECT \n");
        sb.append("(SELECT \n");
        sb.append("IFNULL(SUM(t_cpt_bank_debits.MONEY),0)  \n");
        sb.append("FROM `t_cpt_bank_debits` where 1=1 \n");
        sb.append("AND FK_COMPANY_ID='" + companyId + "' \n");
        sb.append("AND (ENDORSE_FLAG=0  AND LEFT(CREATE_TIME,10) <='" + date + "') \n");
        sb.append("OR (ENDORSE_FLAG=1 AND ENDORSE_MODE=0 AND COLLECTION_ARRIVE_FLAG=0 \n");
        sb.append("AND (COLLECTION_ARRIVE_DATE<='" + date + "' OR COLLECTION_ARRIVE_DATE IS NULL) \n");
        sb.append("AND LEFT(CREATE_TIME,10) <='" + date + "') \n");
        sb.append("OR (ENDORSE_FLAG=1 AND ENDORSE_MODE=0 AND COLLECTION_ARRIVE_FLAG=1 \n");
        sb.append("AND COLLECTION_ARRIVE_DATE>='" + date + "' \n");
        sb.append("AND LEFT(CREATE_TIME,10) <='" + date + "') \n");
        sb.append("OR (ENDORSE_FLAG=1 AND ENDORSE_MODE IN ('1','2') \n");
        sb.append("AND ENDORSE_DATE>='" + date + "' \n");
        sb.append("AND LEFT(CREATE_TIME,10) <='" + date + "') \n");
        sb.append("AND IMPAWN_FLAG=0 \n");
        sb.append("AND DEL_FLAG=0 \n");
        sb.append(") AS debitsIncome, \n");
        sb.append("(SELECT \n");
        sb.append("IFNULL(SUM(t_cpt_bank_debits.MONEY),0)  \n");
        sb.append("FROM `t_cpt_bank_debits` where 1=1 \n");
        sb.append("AND FK_COMPANY_ID = '" + companyId + "' \n");
        sb.append("AND (ENDORSE_FLAG=1 AND ENDORSE_MODE=0 AND COLLECTION_ARRIVE_FLAG=0 \n");
        sb.append("AND DEL_FLAG=0 AND ENDORSE_DATE >='" + date + "') \n");
        sb.append("OR (ENDORSE_FLAG=1 AND ENDORSE_MODE=0 AND COLLECTION_ARRIVE_FLAG=1 \n");
        sb.append("AND DEL_FLAG=0 AND (COLLECTION_ARRIVE_DATE>='" + date + "' AND COLLECTION_ARRIVE_DATE IS NULL)) \n");
        sb.append("OR (ENDORSE_FLAG=1 AND ENDORSE_MODE IN ('1','2') \n");
        sb.append("AND DEL_FLAG=0 AND ENDORSE_DATE='" + date + "' \n");
        sb.append("AND LEFT(CREATE_TIME,10) <='" + date + "') \n");
        sb.append("OR (ENDORSE_FLAG=1 AND  IMPAWN_FLAG=1 \n");
        sb.append("AND DEL_FLAG=0 AND ENDORSE_DATE='" + date + "' \n");
        sb.append("AND LEFT(CREATE_TIME,10) <='" + date + "')\n ");
        sb.append("OR (DEL_FLAG=1 AND ENDORSE_DATE='" + date + "' \n");
        sb.append("AND LEFT(CREATE_TIME,10) <='" + date + "') \n");
        sb.append(") AS debitsExpenditure \n");
        sb.append(") AS A ");
        JSONObject jsonObject = super.queryForJsonList(sb.toString()).get(0);
        return jsonObject;
    }




    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, BankCardEntity.class);
    }
}